package org.webbase.report.service.impl;


import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.webbase.common.SystemConfig;
import org.webbase.report.dao.FormColumnDao;
import org.webbase.report.dao.FormDao;
import org.webbase.report.dao.FormParamDao;
import org.webbase.report.data.excel.ExcelFile;
import org.webbase.report.data.excel.ExcelFile.ExcelStyle;
import org.webbase.report.pojo.Form;
import org.webbase.report.pojo.FormColumn;
import org.webbase.report.pojo.FormParam;
import org.webbase.report.service.FormService;
import org.webbase.system.aop.Logging;
import org.webbase.system.service.impl.BaseServiceImpl;

@Logging
@Service
public class FormServiceImpl extends BaseServiceImpl<Form> implements FormService {

	@Autowired private FormDao formDao;
	@Autowired private FormParamDao formParamDao;
	@Autowired private FormColumnDao formColumnDao;
	
	/**
	 * 查询参数列表
	 */
	public List<FormParam> queryFormParams(String formId){
		return formParamDao.findByFormId(formId);
	}
	
	/**
	 * 生成form对应的sql语句
	 * @param form
	 * @return
	 */
	private String createQuerySqlString(Form form, Map<String, Object> paramMap){
		String sql = null;
		if(form != null){
			List<FormParam> paramList = formParamDao.findByFormId(form.getId());
			sql = form.getHsql();
			for(FormParam param : paramList){
				String paramName = param.getName();
				String paramValue = param.getValue();
				if(paramMap!=null && paramMap.containsKey(paramName)){
					paramValue = String.valueOf(paramMap.get(paramName));
				}
				String paramType = param.getType();
				if("int".equals(paramType)){
					//paramValue = paramValue;
				}else{
					paramValue = String.format("'%s'", paramValue);
				}
				sql = sql.replace(":" + paramName, paramValue);
			}
		}
		return sql;
	}
	
	/**
	 * 创建excel的工作簿
	 * @param form
	 * @param sqlQuerylist
	 * @return
	 */
	private List<Map<String, Object>> createWorkBook(Form form, List<Map<String,Object>> sqlQuerylist){
		List<Map<String, Object>> sheetList = new ArrayList<Map<String, Object>>();
		Map<String, Object> sheet = new HashMap<String, Object>();
		sheet.put(ExcelStyle.SHEET_LABEL, "sheet");
		//列标题
		Map<String, String> sheetStyle = new HashMap<String, String>();
		sheetStyle.put(ExcelStyle.SHEET_HEAD, form.getName());
		sheetStyle.put(ExcelStyle.TITLE_HEIGHT, "500");
		sheetStyle.put(ExcelStyle.ROW_HEIGHT, "500");
		sheet.put(ExcelStyle.SHEET_STYLE, sheetStyle);
		
		List<Map<String, String>> rowStyle = new ArrayList<Map<String, String>>();
		List<Map<String, Object>> data = new ArrayList<Map<String, Object>>();
		if(form.getUseColumn().intValue() == Form.USE_COLUMN_FALSE){
			Set<String> keySet = null;
			for(Map<String,Object> map :sqlQuerylist){
				if(keySet==null){
					keySet = map.keySet();
					//列头属性
					for(String key : keySet){
						Map<String, String> styleItem = new HashMap<String, String>();
						styleItem.put(ExcelStyle.CELL_NAME, key);
						styleItem.put(ExcelStyle.CELL_TITLE, key);
						styleItem.put(ExcelStyle.CELL_WIDTH, "5000");
						rowStyle.add(styleItem);
					}
				}
				Map<String, Object> row = new HashMap<String, Object>();
				for(String key : keySet){
					row.put(key, map.get(key));
				}
				data.add(row);
			}
		}else{
			List<FormColumn> columnList = formColumnDao.findByFormId(form.getId());
			for(FormColumn column : columnList){
				//列头属性
				if(column.getDisplay().intValue()==FormColumn.DISPLAY_TRUE){
					Map<String, String> styleItem = new HashMap<String, String>();
					styleItem.put(ExcelStyle.CELL_NAME, column.getName());
					styleItem.put(ExcelStyle.CELL_TITLE, column.getDescription());
					styleItem.put(ExcelStyle.CELL_WIDTH, column.getWidth() + "");
					rowStyle.add(styleItem);
				}
			}
			for(Map<String,Object> map :sqlQuerylist){
				Map<String, Object> row = new HashMap<String, Object>();
				for(FormColumn column : columnList){
					//数据
					if(column.getDisplay().intValue()==FormColumn.DISPLAY_TRUE){
						String key = column.getName();
						row.put(key, map.get(key));
					}
				}
				data.add(row);
			}
		}
		sheet.put(ExcelStyle.ROW_STYLE, rowStyle);
		sheet.put(ExcelStyle.DATA, data);
		sheetList.add(sheet);
		return sheetList;
	}
	
	/**
	 * 获取下载目录
	 * @return 
	 */
	private String getTempPath() {
		String path = SystemConfig.getProperty("export.excel.downloaddir");
		File file = new File(path);
		if (file.exists() && file.isDirectory()) {
			return path;
		}
		return System.getProperty("java.io.tmpdir");
	}
	
	/**
	 * 创建excel文件
	 */
	@SuppressWarnings("unchecked")
	public String createExcel(String formId, Map<String, Object> paramMap){
		String filename = null;
		Form form = formDao.get(formId);
		if(form!=null){
			String sql = createQuerySqlString(form, paramMap);
			SQLQuery query = formDao.createSqlQuery(sql);
			query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);//结果类型转换为map
			List<Map<String,Object>> sqlQuerylist = query.list();
						
			List<Map<String, Object>> sheetList = createWorkBook(form, sqlQuerylist);
			filename = String.format("%s/EXCEL_%s.xlsx", getTempPath(), System.currentTimeMillis() + "");
			try {
				new ExcelFile().write(filename, sheetList);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return filename;
	}

	/**
	 * 获取自定义列的列表
	 */
	public List<FormColumn> queryFormColumns(String formId) {
		return formColumnDao.findByFormId(formId);
	}
	
}
